﻿using CommonModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;

namespace CommonUtils
{
    public static class ExcelTools
    {
        #region 导入
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isReadFirstRow">是否读取第一行</param>
        /// <returns>返回的DataTable</returns>
        public static List<Dictionary<string, object>> ExcelToList(string fileName, string sheetName = "", bool isReadFirstRow = true)
        {
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            return ExcelToList(fs, fileName, sheetName, isReadFirstRow);
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isReadFirstRow">是否读取第一行</param>
        /// <returns>返回的DataTable</returns>
        public static List<Dictionary<string, object>> ExcelToList(Stream fs, string fileName, string sheetName = "", bool isReadFirstRow = true)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            //List<string> titles = new List<string>();
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            int startRow = 0;
            try
            {
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (!string.IsNullOrWhiteSpace(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    if (firstRow == null)
                    {
                        return list;
                    }
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                    //for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    //{
                    //    titles.Add("column" + i);
                    //}
                    if (isReadFirstRow)
                    {
                        startRow = sheet.FirstRowNum;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum + 1;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        Dictionary<string, object> dic = new Dictionary<string, object>();
                        dic.Add("rownumber", i);
                        for (int j = 0; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null)
                            {
                                //同理，没有数据的单元格都默认是null
                                dic.Add("column" + j, row.GetCell(j).ToString());
                            }
                            else
                            {
                                dic.Add("column" + j, null);
                            }
                        }
                        list.Add(dic);
                    }
                }

                return list;
            }
            catch
            {
                try
                {
                    fs.Close();
                    fs.Dispose();
                }
                catch { }
                throw;
            }
        }

        /// <summary>
        /// 计算导入excel的md5
        /// </summary>
        /// <returns></returns>
        public static string GetMD5HashFromFile(Stream inputStream)
        {
            int bufferSize = 1024 * 16;//自定义缓冲区大小16K  
            byte[] buffer = new byte[bufferSize];
            HashAlgorithm hashAlgorithm = new MD5CryptoServiceProvider();
            int readLength = 0;//每次读取长度  
            var output = new byte[bufferSize];
            while ((readLength = inputStream.Read(buffer, 0, buffer.Length)) > 0)
            {
                //计算MD5  
                hashAlgorithm.TransformBlock(buffer, 0, readLength, output, 0);
            }
            //完成最后计算，必须调用(由于上一部循环已经完成所有运算，所以调用此方法时后面的两个参数都为0)  
            hashAlgorithm.TransformFinalBlock(buffer, 0, 0);
            string md5 = BitConverter.ToString(hashAlgorithm.Hash);
            hashAlgorithm.Clear();
            //inputStream.Close();
            md5 = md5.Replace("-", "");
            return md5;
        }

        #endregion

        #region Excel 导出
        /// <summary>
        /// 创建Excel 指定扩展名
        /// </summary>
        /// <param name="ExtName"></param>
        /// <returns></returns>
        private static IWorkbook CreateExcel(string ExtName = ".xlsx")
        {
            if (ExtName == ".xlsx")
            {
                return new XSSFWorkbook();
            }
            else
            {
                return new HSSFWorkbook();
            }
        }

        public static string ToExcel<T>(ResultSet<T> rs, Func<T, List<string>> func, List<string> Titles, string temppath)
        {

            var workbook = CreateExcel();
            var sheet = workbook.CreateSheet();
            int rowindex = 0;
            if (Titles != null)
            {
                var row = sheet.CreateRow(rowindex);
                for (int col = 0; col < Titles.Count; col++)
                {
                    row.CreateCell(col).SetCellValue(Titles[col]);
                }
                rowindex++;
            }

            if (rs.Value != null && rs.Value.Count > 0)
            {
                foreach (T t in rs.Value)
                {
                    var row = sheet.CreateRow(rowindex);
                    var list = func(t);
                    for (int col = 0; col < list.Count; col++)
                    {
                        row.CreateCell(col).SetCellValue(list[col]);
                    }
                    rowindex++;
                }
            }
            string FullName = Path.Combine(temppath, System.Guid.NewGuid().ToString() + ".xlsx");

            var fs = new FileStream(FullName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs); //写入到excel
            return FullName;
        }

        public static string ToExcel<T>(ResultSet<T> rs, Func<T, List<ExcelValue>> func, Func<IDictionary<string, object>, List<ExcelValue>> footerFunc, List<string> Titles, string temppath)
        {

            var workbook = CreateExcel();
            var sheet = workbook.CreateSheet();
            int rowindex = 0;
            if (Titles != null)
            {
                var row = sheet.CreateRow(rowindex);
                for (int col = 0; col < Titles.Count; col++)
                {
                    row.CreateCell(col).SetCellValue(Titles[col]);
                }
                rowindex++;
            }

            if (rs.Value != null && rs.Value.Count > 0)
            {
                foreach (T t in rs.Value)
                {
                    var row = sheet.CreateRow(rowindex);
                    var list = func(t);
                    for (int col = 0; col < list.Count; col++)
                    {
                        row.CreateCell(col).SetCellValue(list[col].GetValue());
                    }
                    rowindex++;
                }
            }
            if (rs.Footer != null && rs.Footer.Count > 0 && footerFunc != null)
            {
                foreach (var t in rs.Footer)
                {
                    var row = sheet.CreateRow(rowindex);
                    var list = footerFunc(t);
                    for (int col = 0; col < list.Count; col++)
                    {
                        row.CreateCell(col).SetCellValue(list[col].GetValue());
                    }
                    rowindex++;
                }
            }
            string FullName = Path.Combine(temppath, System.Guid.NewGuid().ToString() + ".xlsx");

            var fs = new FileStream(FullName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs); //写入到excel
            return FullName;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rs">集合</param>
        /// <param name="header">表头行，可以多行，可变部分，不变部分直接写入到模板中</param>
        /// <param name="func">返回数据行</param>
        /// <param name="startrow"></param>
        /// <param name="tplpath"></param>
        /// <param name="temppath"></param>
        /// <returns></returns>
        public static string TemplateToExcel<T>(ResultSet<T> rs, List<ExcelHeader> headers, Func<T, List<ExcelData>> func, int startrow, string tplpath, string temppath)
        {

            FileStream file = null;
            try
            {
                file = new FileStream(tplpath, FileMode.Open, FileAccess.Read);
            }
            catch (Exception ex)
            {
                throw new OperateException("无法打开模板", ex);
            }
            var workbook = new XSSFWorkbook(file);
            var sheet = (XSSFSheet)workbook.GetSheet("Sheet1");

            if (sheet == null)
            {
                throw new OperateException("模板格式错误，没有找到sheet1");
            }
            //写入表头
            foreach (var h in headers)
            {
                sheet.GetRow(h.x).GetCell(h.y).SetCellValue(h.data);
            }

            int rowindex = startrow;
            if (rs.Value != null && rs.Value.Count > 0)
            {
                foreach (T t in rs.Value)
                {
                    IRow row = sheet.GetRow(rowindex);

                    if (row == null) { row = sheet.CreateRow(rowindex); }

                    var list = func(t);
                    foreach (var data in list)
                    {
                        row.CreateCell(data.y).SetCellValue(data.data);
                    }
                    rowindex++;
                }
            }

            string FullName = Path.Combine(temppath, System.Guid.NewGuid().ToString() + ".xlsx");

            var fs = new FileStream(FullName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs); //写入到excel
            fs.Flush();
            fs.Close();
            workbook.Close();
            return FullName;
        }

        public static string ToExcel<T>(List<T> list, Func<T, List<string>> func, List<string> Titles, string temppath)
        {

            var workbook = CreateExcel();
            var sheet = workbook.CreateSheet();
            int rowindex = 0;
            if (Titles != null)
            {
                var row = sheet.CreateRow(rowindex);
                for (int col = 0; col < Titles.Count; col++)
                {
                    row.CreateCell(col).SetCellValue(Titles[col]);
                }
                rowindex++;
            }

            if (list != null && list.Count > 0)
            {
                list.ForEach(t =>
                {
                    var row = sheet.CreateRow(rowindex);
                    var list2 = func(t);
                    for (int col = 0; col < list2.Count; col++)
                    {
                        row.CreateCell(col).SetCellValue(list2[col]);
                    }
                    rowindex++;
                });

            }
            string FullName = Path.Combine(temppath, System.Guid.NewGuid().ToString() + ".xlsx");

            var fs = new FileStream(FullName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs); //写入到excel
            return FullName;
        }


        public static List<ExcelData> Add(this List<ExcelData> list, int y, string data)
        {
            list.Add(new ExcelData(y, data));
            return list;
        }
        public static List<ExcelHeader> Add(this List<ExcelHeader> list, int x, int y, string data)
        {
            list.Add(new ExcelHeader(x, y, data));
            return list;
        }
        #endregion
    }


    public class ExcelValue
    {
        public ExcelValue()
        {

        }
        public ExcelValue(object data, ExcelValueType type = ExcelValueType.Text)
        {
            this.value = data;
            this.type = type;
        }
        public object value { get; set; }
        public ExcelValueType type { get; set; }

        public dynamic GetValue()
        {
            if (type == ExcelValueType.Numeric)
            {
                return Convert.ToDouble(value);
            }
            else
            {
                return Convert.ToString(value);
            }
        }
        public static ExcelValue FromDictionary(IDictionary<string,object> dic,string key,ExcelValueType type = ExcelValueType.Text)
        { 
            if (dic!=null && dic.ContainsKey(key))
            {
                return new ExcelValue(dic[key], type);
            }
            return new ExcelValue();
        }
    }
    public enum ExcelValueType
    {
        Text = 0,
        Numeric = 1,
        DateTime = 2
    }
}
